﻿//Copyright (C) <2013>  <jonathan cleeve norton> All Rights Reserved 
//Contact jon.norton@fin-plus.co.uk website <http://www.fin-plus.co.uk/>
using System;
using System.Collections.Concurrent;
using System.Data.SqlServerCe;
using System.Reflection;
using FinPlusAssembler;
using FinPlusInterfaces;

namespace FinPlusConnDB
{
    public sealed class FinPlusConnDatabase : IDisposable, IConnDatabase
    {
        public event EventHandler QueryUpdate;

        private ConcurrentBag<IQuery> _finPlusQuerys = new ConcurrentBag<IQuery>();
        private ConcurrentDictionary<string, string> _podGroupMap;
        private SqlCeConnection _conn;
        private ITransport _tran;
        private Env _env;
        private readonly Config _config;
        private readonly string _user, _connString;

        //construct
        public FinPlusConnDatabase() : this(null) { }

        public FinPlusConnDatabase(string connString)
        {
            _connString = connString;
            _user = Environment.UserName;

            var path = Assembly.GetExecutingAssembly().ConfigPath();
            _config = path.ConfigXml(out _env);

            Initialise();
        }

        public IQuery Query(QueryType queryType, string group, DateTime date, string where, ConType conType = ConType.None)
        {
            switch (queryType)
            {
                case QueryType.Select: return Select(group, where, conType);
                case QueryType.SelectAsOf: return SelectAsOf(group, date, where, conType);
                case QueryType.SelectFrom: return SelectFrom(group, date, where, conType);
                default: return null;
            }
        }

        //public 
        public string Save(IPod value)
        {
            try
            {
                if (value.Name.Equals(Common.PodMap.ToString())) 
                    return PodMapSave(value);
                return PodSave(value);
            }
            catch(Exception e)
            {
                return e.ToString();
            }
        }

        public string Close(IPod value) 
        {
            try
            {
                if (value.Name.Equals(Common.PodMap.ToString())) 
                    return "Cant perform action on MapPod";
                return PodClose(value);
            }
            catch (Exception e)
            {
                return e.ToString();
            }
        }

        public IQuery SavePod(string group, string where, ConType conType = ConType.None)
        {
            var query = new FinPlusQuery(_conn, _tran, QueryType.SavePod, group, DateTime.MinValue, DateTime.MinValue, where, conType);
            _finPlusQuerys.Add(query); 
  
            return query; 
        }

        public IQuery Select(string group, string where, ConType conType = ConType.None)
        {
            var query = new FinPlusQuery(_conn, _tran, QueryType.Select, group, DateTime.MinValue, DateTime.MinValue, where, conType);
            if (query.ConType != ConType.None) 
                _finPlusQuerys.Add(query); 
            query.Execute(); 

            return query; 
        }

        public IQuery SelectAsOf(string group, DateTime asOf, string where, ConType conType = ConType.None)
        {
            var q = new FinPlusQuery(_conn, _tran, QueryType.Select, group, asOf, DateTime.MinValue, where, conType);
            q.Execute(); 

            return q;
        }

        public IQuery SelectFrom(string group, DateTime from, string where, ConType conType = ConType.None)
        {
            var query = new FinPlusQuery(_conn, _tran, QueryType.Select, group, DateTime.MinValue, from, where, conType);
            if (query.ConType != ConType.None) 
                _finPlusQuerys.Add(query); 
            query.Execute(); 
            
            return query;
        }

        public IQuery SelectId(string group, int Id)
        {
            var query = Select(group, @"Name = 'Id' AND Value = '" + Id.ToString() + @"'", ConType.None);
            if (query.ConType != ConType.None)
                _finPlusQuerys.Add(query); 
            query.Execute();

            return query;
        }

        public void Dispose()
        {
            if (_conn != null)
            {
                _conn.Close();
                _conn.Dispose();
                _conn = null;
            }
        }

        public void Dispose(IQuery query)
        {
            _finPlusQuerys.TryTake(out query);
        }

        //private
        private void Initialise()
        {
            if (_tran == null)
            {
                _tran = IOC.New<ITransport>(_config["messagingsettings"]["address"], int.Parse(_config["messagingsettings"]["port"]));
                _tran.Listener.MessageRecieved += Query_Update;
            }

            _conn = new SqlCeConnection();
            if (!string.IsNullOrEmpty(_connString))
                _conn.ConnectionString = _connString;
            else
                _conn.ConnectionString = string.Format(@"Data Source = '{0}';Password='{1}';", _config["databasesettings"]["datasource"], _config["databasesettings"]["password"]);

            _conn.Open();

            if (_podGroupMap == null)
                _podGroupMap = PodGroupMap();
        }

        private string PodSave(IPod value)
        {
            try
            {
                if (!_podGroupMap.ContainsKey(value.Name)) return value.Error = @"Table not setup use =PodMap(?, '" + value.Name + "')";

                var q = new FinPlusQuery(value, _conn, _tran, QueryType.SavePod, _podGroupMap[value.Name], _user);
                q.Execute();
                return value.Id.ToString();
            }
            catch (Exception e)
            {
                return e.ToString();
            }
        }

        private string PodClose(IPod value)
        {
            try
            {
                if( value.Id < 0 ) 
                    return "You cant delete pod not saved";

                var q = new FinPlusQuery(value, _conn, _tran, QueryType.ClosePod, _podGroupMap[value.Name], _user);
                q.Execute();

                return "Deleted " + value.Id.ToString();
            }
            catch (Exception e)
            {
                return e.ToString();
            }
        }

        private string PodMapSave(IPod value)
        {
            var Type = value["Name"].ToString();
            if (!_podGroupMap.ContainsKey(Type))
            {
                var podGroup = value[Common.Group.ToString()].ToString();
                PodGroupCreateTables(podGroup);
                _podGroupMap[Type] = podGroup;
                return PodSave(value);
            }

            return value.Error = "Already Mapped";
        }

        private ConcurrentDictionary<string, string> PodGroupMap()
        {
            var map = new ConcurrentDictionary<string, string>();
            map[Common.PodMap.ToString()] = Common.PodMap.ToString();

            PodGroupCreateTables(Common.PodMap.ToString());
            var q = Select("PodMap", string.Empty, ConType.None);

            q.Execute();
            foreach (var f in q.Values.Values)
                map.TryAdd(f["Name"].ToString(), f[Common.Group.ToString()].ToString());

            return map;
        }

        private void PodGroupCreateTables(string group)
        {
            try
            {
                if (group == string.Empty || group.Equals(Common.PodMap.ToString())) return;


                //TODO put both creation tables together so no incosistency if one fails
                var sql1 = @"CREATE TABLE " + group + @"Ids" +
                            @"(
                                    [Id] int primary key IDENTITY(1,1) NOT NULL,
                                    [TimeStamp] datetime NOT NULL,
                                    [User] nvarchar(50) NOT NULL,
                                    [Closed] datetime,
                                    [ClosedBy] nvarchar(50), 
                                    [LastChanged] datetime NOT NULL
                            )";

                lock (_conn) using (var cmd = new SqlCeCommand(sql1, _conn)) cmd.ExecuteNonQuery();

                var sql2 = @"CREATE TABLE " + group + @"Beans" +
                            @"(
                                    [Id] int NOT NULL,
                                    [Index] int NOT NULL,
                                    [Name] nvarchar(50) NOT NULL,
                                    [Value] nvarchar(200) NOT NULL,
                                    [Type] nvarchar(50) NOT NULL,
                                    [TimeStamp] datetime NOT NULL,
                                    [User] nvarchar(50) NOT NULL,
                                    CONSTRAINT pk_" + group + @"Beans PRIMARY KEY ([Id], [Name], [TimeStamp])
                            )";
                //

                lock (_conn) using (var cmd = new SqlCeCommand(sql2, _conn)) cmd.ExecuteNonQuery();


            }
            catch 
            {
                  //utilities need to add logging
            }

        }

        private void QueryUpdates(IMessage msg)
        {
            var pod = IOC.New<IPod>(msg);

            if (!_podGroupMap.ContainsKey(pod.Name)) return;

            var group = _podGroupMap[pod.Name];

            foreach (var q in _finPlusQuerys)
                if (q.Group == group && q.Update(pod) && QueryUpdate != null)
                    QueryUpdate.Invoke(this, new ConnEventArgs(q, ConnEventType.UpdateQuery)); 
        }

        //events
        private void Query_Update(object s, EventArgs a)
        {
            var msgEvent = (MessageEventArgs)a;
            QueryUpdates(msgEvent.Message);
        }
    }
}
